package com.sino.dataworks.util;

import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import com.aliyuncs.utils.StringUtils;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;

import java.util.*;

public class MultiSheetExcelExporter {
    // 模拟数据存储结构
   public static class SheetMeta {
        String sheetName;
        String chineseName;
        String remark;
        List<Map<String, String>> fields;

        public String getSheetName() {
            return sheetName;
        }

        public void setSheetName(String sheetName) {
            this.sheetName = sheetName;
        }

        public String getChineseName() {
            return chineseName;
        }

        public void setChineseName(String chineseName) {
            this.chineseName = chineseName;
        }

        public String getRemark() {
            return remark;
        }

        public void setRemark(String remark) {
            this.remark = remark;
        }

        public List<Map<String, String>> getFields() {
            return fields;
        }

        public void setFields(List<Map<String, String>> fields) {
            this.fields = fields;
        }

        public SheetMeta() {
        }
        public SheetMeta(String sheetName, String chineseName, String remark, List<Map<String, String>> fields) {
            this.sheetName = sheetName;
            this.chineseName = chineseName;
            this.remark = remark;
            this.fields = fields;
        }
    }



    public static void main(String[] args) {
        // 准备测试数据（可扩展多个Sheet）
        List<SheetMeta> allSheets = new ArrayList<>();

        // Sheet1: 经销商授信主题表
        List<Map<String, String>> sheet1Data = new ArrayList<>();
        // 添加示例数据（实际应根据Excel内容填充）
        sheet1Data.add(createFieldMap("dealer_no", "经销商编号", "string", "dwd_ws_ctrct_dtl", "批售合同明细", "CUST_NO", "经销商编号"));
        sheet1Data.add(createFieldMap("ctrct_no", "授信合同号", "string", "dwd_ws_ctrct_dtl", "批售合同明细", "CONTRACT_NO", "授信合同号"));
        // 添加更多字段数据...

        allSheets.add(new SheetMeta(
                "dws_ws_crdt_gnt_theme_tbl",
                "经销商授信主题表",
                "",
                sheet1Data
        ));

        // Sheet2: 其他表示例（可扩展）
        List<Map<String, String>> sheet2Data = new ArrayList<>();
        sheet2Data.add(createFieldMap("field1", "字段1", "string", "src_table1", "源表1", "src_field1", "源字段1"));
        allSheets.add(new SheetMeta(
                "other_table",
                "其他表示例",
                "示例备注",
                sheet2Data
        ));

        // 创建Excel写入器
        ExcelWriter writer = ExcelUtil.getWriter(true);
        writer.setDestFile(new java.io.File("output.xlsx"));

        // 1. 创建目录Sheet
        createCatalogSheet(writer, allSheets);

        // 2. 创建各数据Sheet
        for (int i = 0; i < allSheets.size(); i++) {
            SheetMeta sheetMeta = allSheets.get(i);
            createDataSheet(writer, sheetMeta, i+1); // +1因为目录是第一个sheet
        }

        // 3. 设置全局样式
        setGlobalStyle(writer);

        // 写入文件并关闭
        writer.close();
    }

    public static void createCatalogSheet(ExcelWriter writer, List<SheetMeta> allSheets) {
        writer.renameSheet(0, "目录");
        writer.setSheet("目录");
        // 写入目录表头
        writer.writeHeadRow(Arrays.asList("表名", "中文名", "备注"));
        // 写入目录数据并添加超链接
        for (int i = 0; i < allSheets.size(); i++) {
            SheetMeta sheet = allSheets.get(i);
            int row = i + 1; // 表头占第0行
            // 创建超链接
            Hyperlink link = writer.getWorkbook().getCreationHelper()
                    .createHyperlink(HyperlinkType.DOCUMENT);
            link.setAddress("'" + sheet.chineseName + "'!A1");

            writer.writeCellValue(0, row, sheet.sheetName);
            if(StringUtils.isEmpty(sheet.chineseName)){
                sheet.chineseName=sheet.sheetName;
            }
            writer.writeCellValue(1, row, sheet.chineseName);
            Cell cell = writer.getCell(1, row);
            cell.setCellValue(sheet.chineseName);
            cell.setHyperlink(link);
            writer.writeCellValue(2, row, sheet.remark);
        }
    }

    public static void createDataSheet(ExcelWriter writer, SheetMeta sheetMeta, int sheetIndex) {
       if(StringUtils.isEmpty(sheetMeta.chineseName)){
           sheetMeta.chineseName=sheetMeta.sheetName;
       }
        writer.setSheet(sheetMeta.chineseName);
        writer.setSheet(sheetIndex);

        // 合并表头单元格
      //  writer.merge(0, 0, 0, 6, "表名称：" + sheetMeta.sheetName, true);
      //  writer.merge(1, 1, 0, 6, "表中文名称：" + sheetMeta.chineseName, true);
        writer.writeRow(Arrays.asList(
                "表名称:", sheetMeta.sheetName, "表中文名称:",
                sheetMeta.chineseName, "", "", ""
        ));
        // 写入字段表头
        writer.writeRow(Arrays.asList(
                "字段名", "字段中文名", "数据类型",
                "源表名", "源表中文名", "源表字段名", "源表字段中文名"
        ));

        // 写入数据行
        for (Map<String, String> field : sheetMeta.fields) {
            writer.writeRow(Arrays.asList(
                    field.get("fieldName"),
                    field.get("fieldChinese"),
                    field.get("dataType"),
                    field.get("srcTable"),
                    field.get("srcTableChinese"),
                    field.get("srcField"),
                    field.get("srcFieldChinese")
            ));
        }

        // 自动调整列宽
        writer.autoSizeColumnAll();
    }

    public static void setGlobalStyle(ExcelWriter writer) {
        StyleSet style = writer.getStyleSet();

        // 设置超链接样式
        CellStyle linkStyle = style.getCellStyle();
        Font font = writer.createFont();
        font.setUnderline(Font.U_SINGLE);
        font.setColor(IndexedColors.BLUE.getIndex());
        linkStyle.setFont(font);

        // 设置表头样式
        CellStyle headerStyle = style.getHeadCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    }

    public static Map<String, String> createFieldMap(String fieldName, String fieldChinese, String dataType,
                                                      String srcTable, String srcTableChinese,
                                                      String srcField, String srcFieldChinese) {
        Map<String, String> map = new HashMap<>();
        map.put("fieldName", fieldName);
        map.put("fieldChinese", fieldChinese);
        map.put("dataType", dataType);
        map.put("srcTable", srcTable);
        map.put("srcTableChinese", srcTableChinese);
        map.put("srcField", srcField);
        map.put("srcFieldChinese", srcFieldChinese);
        return map;
    }
}